package com.linln.modules.shopsService.repository;

import com.linln.modules.shopsService.domain.Shops;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

/**
 * @author zsc
 * @date 2020/11/19
 */
@PersistenceContext
public interface
ShopsRepository extends JpaRepository<Shops, String> {

    /**
     * 添加店铺用户
     *
     * @param id
     * @param shopsId
     * @param userId
     * @return
     */
    @Modifying
    @Query(nativeQuery = true, value = "insert into tbl_shop_user values (?1,?2,?3,?4)")
    Integer addShopUser(String id, String shopsId, Long userId, String time);

    /**
     * 查询店铺人员是否存在或存在多个
     *
     * @param
     * @param userId
     * @return
     */
    @Query(nativeQuery = true, value = "select count(*) from tbl_shop_user where user_id = ?1")
    Integer searchShopUser(Long userId);

    /**
     * 查询店铺店长是否存在或存在多个
     *
     * @param
     * @param userId
     * @return
     */
    @Query(nativeQuery = true, value = "select count(*) from tbl_shop_user tsu left join sys_user_role sur on tsu.user_id = sur.user_id  where sur.user_id = ?1 and role_id = 3")
    Integer searchShopBoss(Long userId);


    /**
     * 分页查询店铺所有店员
     *
     * @param page
     * @param limit
     * @param shopId
     * @return
     */
    @Query(value = "select user.id as userId,su.shop_user_id as shopUserId,user.username,user.nickname,role.title as rolename ,user.sex,user.phone ,su.create_date from sys_user user " +
            " left join tbl_shop_user su on su.user_id = user.id " +
            " left join sys_user_role ur on user.id = ur.user_id " +
            " left join sys_role role on role.id = ur.role_id " +
            " where su.shop_id = ?3" +
            " and role.id = 5 " +
            " ORDER BY create_date DESC " +
            "limit ?1 ,?2 ", nativeQuery = true)
    public List<Map<String, String>> findAllShop(Integer page, Integer limit, String shopId);

    @Query(value = "select count(su.shop_user_id )from sys_user user " +
            " left join tbl_shop_user su on su.user_id = user.id " +
            " left join sys_user_role ur on user.id = ur.user_id " +
            " left join sys_role role on role.id = ur.role_id " +
            " where su.shop_id = ?1" +
            " and (role.id = 5 or role.id = 3)  ", nativeQuery = true)
    int findAllShopCount(String shopsId);

    /**
     * 为用户创建角色
     *
     * @param userId
     * @param roleId
     * @return
     */
    @Modifying
    @Query(nativeQuery = true, value = "insert into sys_user_role values (?1,?2)")
    public Integer createUserRole(Long userId, Long roleId);

    /**
     * 为用户移除店铺角色
     *
     * @param userId
     */
    @Modifying
    @Query(nativeQuery = true, value = "delete from sys_user_role where user_id = ?1 and role_id = ?2")
    void delShopRole(Long userId, Long roleId);

    /**
     * 批量为用户移除店铺角色
     *
     * @param userIds
     */
    @Modifying
    @Query(nativeQuery = true, value = "delete from sys_user_role where user_id in(:userIds) and role_id = 5")
    void delAllShopRole(@Param(value = "userIds") List<String> userIds);

    /**
     * 移除店铺人员
     *
     * @param shopUserId
     */
    @Modifying
    @Query(nativeQuery = true, value = "delete from tbl_shop_user where shop_user_id = ?1")
    void delShopUser(String shopUserId);

    /**
     * 查询用户角色关联
     *
     * @param userId
     * @param roleId
     * @return
     */
    @Query(nativeQuery = true, value = "select count(*) from sys_user_role where user_id = ?1 and role_id = ?2")
    int searchShopRole(Long userId, Long roleId);

    /**
     * 获取位置数据
     *
     * @return
     */
    @Query(nativeQuery = true, value = "select area_id as id ,name,area_level*1 as level , whole_name as wholeName  from tbl_area where  if(?1 !='',parent_id = ?1,1=1) and if(?2 !='',area_id = ?2,1=1)")
    List<Map<String, Object>> getAreaData(String parentId, String areaId);

    /**
     * 更具userid搜索店铺id
     *
     * @param userId
     * @return
     */
    @Query(nativeQuery = true, value = "select shop_id from tbl_shop_user where user_id = ?1")
    String byUsersearchShopId(Long userId);

    /**
     * 获取店长
     *
     * @param shopId
     * @param roleId
     * @return
     */
    @Query(nativeQuery = true, value = "select user.id,user.nickName,su.shop_user_id,user.phone from sys_user user " +
            " left join sys_user_role ur on user.id = ur.user_id " +
            " left join tbl_shop_user su on su.user_id = ur.user_id " +
            " where su.shop_id = ?1 and ur.role_id = ?2")
    Map<String, Object> getManager(String shopId, Long roleId);

    /**
     * 查询店铺所有用户id
     *
     * @param shopId
     * @return
     */
    @Query(value = "select user.id as userId from sys_user user " +
            " left join tbl_shop_user su on su.user_id = user.id " +
            " left join sys_user_role ur on user.id = ur.user_id " +
            " left join sys_role role on role.id = ur.role_id " +
            " where su.shop_id = ?1" +
            " and role.id = 5 "
            , nativeQuery = true)
    List<String> getAllShop(String shopId);


    @Modifying
    @Query(nativeQuery = true, value = "delete from tbl_shop_user where shop_id = ?1")
    void delShopAllUser(String shopId);

    @Modifying
    @Query(nativeQuery = true, value = "update tbl_shop set del_flag = 1 where shop_id = ?1")
    void deleteFlagById(String shopId);


    @Query(nativeQuery = true, value = "select area_id from tbl_area where name = ?1")
    public String getAreass(String name);


    @Query(nativeQuery = true, value = "select area_id from tbl_area where name = ?1 AND parent_id = ?2")
    public String getAreass(String name, String parentId);

    @Query(nativeQuery = true, value = "select service_type_id as value , service_type_name as name from tbl_service_type where is_shop_available = 1")
    List<Map<String, Object>> getShopType();

    @Query(nativeQuery = true, value = "select s.shop_id,s.audit_mark,s.shop_name from tbl_shop s " +
            " left join tbl_shop_user su on su.shop_id = s.shop_id"
            +" where su.user_id = ?1 and s.del_flag = '0' order by s.create_date asc")
    Map<String,Object> getShopByUser(Long userId);

    @Query(nativeQuery = true, value = "select s.shop_id,s.audit_mark,s.shop_name from tbl_shop s " +
            " left join tbl_shop_user su on su.shop_id = s.shop_id"
            +" where su.user_id = ?1 and s.del_flag = '0' order by s.create_date asc")
    List<Map<String,Object>> getShopsByUser(Long userId);
    @Transactional
    @Modifying
    @Query(nativeQuery = true, value = "insert into tbl_area2(area_id,parent_id,area_level,name,brief_name,per_pin_yin) values(?1,?2,?3,?4,?5,?6)")
    void saveArea(String area_id ,String parent_id,String area_level,String name,String brief_name,String per_pin_yin);


    @Query(nativeQuery = true, value = "select area_id from tbl_area where name  = ?1")
    String getAddressbyAreaId(String address);

    @Query(nativeQuery = true, value = "select shop_name from tbl_shop where shop_id = ?1")
    String getShopNameByShopId(String shopId);

    List<Shops> findByShopIdIn(String[] ids);
}
